package com.funeral.diyaccount.base.dao;

import com.funeral.diyaccount.base.exception.BaseDaoException;
import com.funeral.diyaccount.base.exception.DaoException;
import com.funeral.diyaccount.common.page.pojo.PageObject;
import org.apache.commons.lang.StringUtils;

import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.log4j.Logger;
import org.mybatis.spring.support.SqlSessionDaoSupport;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.security.util.FieldUtils;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.io.Serializable;
import java.lang.reflect.Field;
import java.lang.reflect.Type;
import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

/**
 * 该工程的baseDao <br>
 *     该类所有方法均为protected范围以内,即无法被直接注入引用，只可继承
 * @param <T> 实体类
 * @param <PK> 该实体类所使用的【主键类】
 */
@Repository("baseDao")
public class BaseDao<T,PK extends Serializable> extends SqlSessionDaoSupport implements Serializable{

    private BaseDaoInfo daoInfo;

    /**
     * 就当更新版本号好了..
     */
    private static final long serialVersionUID = 20170522104241L;

    /**
     * BaseDao的日志输出类
     */
    private static final Logger logger = Logger.getLogger(BaseDao.class);

    /**
     * Spring 的JDBC模板bean
     */
    @Resource
    private JdbcTemplate jdbcTemplate;

//    /**
//     * 用于获取信息格式处理的RowMapper接口 - 需填充
//     */
//    private RowMapper<T> rowMapper;
//
//    /**
//     * 表名
//     */
//    private String tableName;
//
//    /**
//     * 该表所使用的主键名
//     */
//    private String idName;
//
//    /**
//     * 该表使用的序列名
//     */
//    private String sequenceName;


    @Resource
    protected void setSessionFactory(SqlSessionFactory sqlSessionFactory){
        super.setSqlSessionFactory(sqlSessionFactory);
    }


    /**
     * 用于执行保存、删除和更新语句的方法
     * @param sql
     * @return 执行的成功的语句数量
     * @throws BaseDaoException
     */
    protected int executeSQL(String sql) throws BaseDaoException{
        if(StringUtils.isBlank(sql)){throw new BaseDaoException("Invalid sql!");}
        logger.info(sql);
        return this.jdbcTemplate.update(sql);
    }

    /**
     * 用于执行保存、删除和更新语句的方法（带参数）
     * @param sql
     * @param parameters
     * @return 执行的成功的语句数量
     * @throws BaseDaoException
     */
    protected int executeSQL(String sql,final Object... parameters) throws BaseDaoException {
        if(StringUtils.isBlank(sql)){throw new BaseDaoException("Invlaid sql!");}
        if(parameters == null || parameters.length == 0){throw new BaseDaoException("Invalid parameters!");}
        logger.info(sql);
        return this.jdbcTemplate.update(sql,parameters);
    }

    /**
     * 用于批量执行保存和更新语句
     * @param sqls
     * @return 每条sql执行成功数量
     * @throws BaseDaoException
     */
    protected int[] executeListSQL(String[] sqls) throws BaseDaoException{
        if(sqls == null || sqls.length == 0 ){throw new BaseDaoException("Invalid sqls!");}
        for(String sql:sqls){
            logger.info(sql);
        }
        return this.jdbcTemplate.batchUpdate(sqls);
    }

    /**
     * 用于批量执行保存和更新语句(带参数)
     * @param sql
     * @param paramrslist
     * @return
     * @throws BaseDaoException
     */
    protected int[] executeListSQL(String sql, Collection<Object[]> paramrslist) throws BaseDaoException{
        if(StringUtils.isBlank(sql)){throw new BaseDaoException("Invalid sql!");}
        List<Object[]> list= new ArrayList<Object[]>();
        for(Object[] objs :paramrslist){
            list.add(objs);
        }
        logger.info(sql);
        return this.jdbcTemplate.batchUpdate(sql,list);
    }

    /**
     * 通过主键获取该实体类
     * @param pk
     * @return
     * @throws BaseDaoException
     */
    protected T getEntityByPK(PK pk) throws BaseDaoException{
        if(pk == null){throw new BaseDaoException("Invalid primary key!");}
        if(StringUtils.isBlank(daoInfo.getTableName())){throw new BaseDaoException("tableName is null,please set tableName first!");}
        StringBuffer sql = new StringBuffer("select * from ");
        sql.append(daoInfo.getTableName());
        sql.append(" where ");
        sql.append(daoInfo.getIdName());
        sql.append(" = ?");
        Object pkobj[] = new Object[1];
        pkobj[0] = pk;
        logger.info(sql.toString());
        List<T> list = this.jdbcTemplate.query(sql.toString(),pkobj,daoInfo.getRowMapper());
        if(list == null || list.isEmpty()){return null;}
        if(list.size() > 1){throw new BaseDaoException("Error: this query is list!");}

        return list.get(0);
    }

    /**
     * 通过sql语句和参数获得该单一实体类
     * @param sql
     * @param parameters
     * @return
     * @throws BaseDaoException
     */
    protected T queryUniqueSQL(String sql,final Object... parameters) throws BaseDaoException{
        if(StringUtils.isBlank(sql)){throw new BaseDaoException("Invalid sql!");}
        if(parameters == null || parameters.length == 0){throw new BaseDaoException("Invalid parameters!");}
        if(daoInfo.getRowMapper() == null){throw new BaseDaoException("RowMapper is null, Please set RowMapper!");}
        logger.info(sql);
        List<T> list = this.jdbcTemplate.query(sql,parameters,daoInfo.getRowMapper());
        if(list == null || list.isEmpty()){return null;}
        if(list.size() > 1){throw new BaseDaoException("Error: this query is list!");}

        return list.get(0);

    }

    /**
     * 保存实体类到数据库
     * @param t
     * @return
     * @throws BaseDaoException
     */
    protected PK saveEntity(T t)throws BaseDaoException{
        if(t == null){throw new BaseDaoException("Can't save null!");}
        Class clzz = t.getClass();
        Field[] fs = clzz.getDeclaredFields();
        StringBuffer sql = new StringBuffer();
        StringBuffer valuse = new StringBuffer();
        sql.append("insert into ");
        sql.append(daoInfo.getTableName());
        sql.append(" ( ");
        valuse.append(" values ( ");
        List<Object> params = new ArrayList<Object>();
        boolean idFieldBlank = true;
        String tmp = "";
        for(Field f:fs){
            try {
                Object o = FieldUtils.getFieldValue(t, f.getName());
                if(o!= null){
                    if(daoInfo.getIdName().equals(f.getName())){
                        idFieldBlank = false;
                    }
                    sql.append(tmp);
                    sql.append(f.getName());
                    valuse.append(tmp);
                    params.add(o);
                    valuse.append("?");
                    tmp = ",";
                }
            }catch (IllegalAccessException iae){
                iae.printStackTrace();
                throw new BaseDaoException(iae.getMessage());
            }
        }
        Object pk = null;
        if(idFieldBlank){
            StringBuffer nextid_sql = new StringBuffer();
            nextid_sql.append("select ");
            nextid_sql.append(daoInfo.getSequenceName());
            nextid_sql.append(".nextval as ");
            nextid_sql.append(daoInfo.getIdName());
            nextid_sql.append(" from dual");
            List<Object[]> objs = this.queryListSql(nextid_sql.toString());
            pk = objs.get(0)[0];
            sql.append(tmp);
            sql.append(daoInfo.getIdName());
            valuse.append(tmp);
            valuse.append("?");
            params.add(pk);
            tmp = ",";
        }else{
            try {
                pk = FieldUtils.getFieldValue(t, daoInfo.getIdName());
            }catch (IllegalAccessException iae){
                iae.printStackTrace();
                throw new BaseDaoException(iae.getMessage());
            }
        }
        sql.append(" ) ");
        valuse.append(" ) ");
        sql.append(valuse);

        //针对Long 类型的pk ,数据库取到的类型为Bigdecimal 的转化
        try {
            Field idField = clzz.getDeclaredField(daoInfo.getIdName());
            Type type = idField.getGenericType();
            if(pk instanceof BigDecimal && type.equals(Long.class)){
                pk = ((BigDecimal) pk).longValue();
            }
        }catch(NoSuchFieldException nsfe){
            nsfe.printStackTrace();
            throw new BaseDaoException(nsfe.getMessage());
        }
        this.executeSQL(sql.toString(),params.toArray());
        return (PK) pk;
    }

    /**
     * 删除实体
     * @param pk
     * @throws BaseDaoException
     */
    protected void delEntity(PK pk)throws BaseDaoException{
        if(pk == null){
            throw new BaseDaoException("Primary key null");
        }
        StringBuffer sql = new StringBuffer();
        sql.append("delete from ");
        sql.append(daoInfo.getTableName());
        sql.append(" where ");
        sql.append(daoInfo.getIdName());
        sql.append(" = ? ");
        this.executeSQL(sql.toString(),pk);

    }


    /**
     * 执行原生Sql语句以及相应参数获得该实体类集合
     * @param sql
     * @param parameters
     * @return
     * @throws BaseDaoException
     */
    protected List<T> queryListSQL(String sql,final Object... parameters) throws BaseDaoException{
        if(StringUtils.isBlank(sql)){throw new BaseDaoException("Invalid sql!");}
        if(parameters == null || parameters.length == 0){throw new BaseDaoException("Invalid parameters!");}
        if(daoInfo.getRowMapper() == null){throw new BaseDaoException("RowMapper is null, Please set RowMapper!");}
        logger.info(sql);
        return this.jdbcTemplate.query(sql,parameters,daoInfo.getRowMapper());
    }



    /**
     * 通过分页形式执行原生sql语句以及相应参数获得该实体类集合
     * @param sql
     * @param pageObject
     * @param parameters
     * @return
     * @throws BaseDaoException
     */
    protected PageObject<T> queryListSqlWithPage(String sql, PageObject<T> pageObject, final Object...parameters) throws BaseDaoException{
        if(StringUtils.isBlank(sql)){throw new BaseDaoException("Invalid sql!");}
        //if(parameters == null ){throw new BaseDaoException("Invalid parameters!");}
        if(pageObject == null ){throw new BaseDaoException("Invalid pageObject!");}
        Integer page = pageObject.getPage();
        Integer rows = pageObject.getRows();

        StringBuffer countSql = new StringBuffer("select count(1) as countSize from ( ? ) ");
        countSql.replace(countSql.indexOf("?"),countSql.indexOf("?")+1,sql);
        logger.info(countSql.toString());
        Integer total = this.jdbcTemplate.queryForInt(countSql.toString(),parameters);
        if(total == null || total == 0){
            return pageObject;
        }
        StringBuffer tmpSql = new StringBuffer("select * from ( :sql  ) where rowid in (select rid from ( select rownum rn, rowid rid from ( :sql ) where rownum <= ? ) where rn > ? )");
        tmpSql.replace(tmpSql.indexOf(":sql"),tmpSql.indexOf(":sql")+4,sql);
        tmpSql.replace(tmpSql.indexOf(":sql"),tmpSql.indexOf(":sql")+4,sql);
        List<Object> tmpparameters = new ArrayList<Object>();
        for(int i = 0 ;i < 2; i ++){
            for(Object param:parameters){
                tmpparameters.add(param);
            }
        }
        Integer rownumStart = rows*(page-1);
        Integer rownumEnd = rows*page;
        tmpparameters.add(rownumEnd);
        tmpparameters.add(rownumStart);

        List<T> result = this.queryListSQL(tmpSql.toString(),tmpparameters.toArray());

        pageObject.setTotal(total);
        pageObject.setRowsList(result);

        return pageObject;

    }

    /**
     * 执行原生sql语句和相应参数，并自定义rowMapper 查询序列集合
     * @param sql
     * @param rowMapper
     * @param parameters
     * @return
     * @throws BaseDaoException
     */
    protected List queryListSQL(String sql,RowMapper rowMapper,final Object... parameters) throws BaseDaoException{
        if(StringUtils.isBlank(sql)){throw new BaseDaoException("Invalid sql!");}
        if(rowMapper == null){throw new BaseDaoException("Invalid rowMapper!");}
        if(parameters == null || parameters.length == 0){throw new BaseDaoException("Invalid parameters!");}
        logger.info(sql);
        return this.jdbcTemplate.query(sql,rowMapper,parameters);
    }

    /**
     * 通过分页形式执行原生sql语句和相应参数，并自定义rowMapper 查询序列集合
     * @param sql
     * @param rowMapper
     * @param parameters
     * @return
     * @throws BaseDaoException
     */
    protected PageObject queryListSqlWithPage(String sql, PageObject pageObject, RowMapper rowMapper,final Object...parameters) throws BaseDaoException{
        if(StringUtils.isBlank(sql)){throw new BaseDaoException("Invalid sql!");}
        if(parameters == null || parameters.length == 0){throw new BaseDaoException("Invalid parameters!");}
        if(rowMapper == null){throw new BaseDaoException("Invalid rowMapper!");}
        if(pageObject == null ){throw new BaseDaoException("Invalid pageObject!");}
        Integer page = pageObject.getPage();
        Integer rows = pageObject.getRows();

        StringBuffer countSql = new StringBuffer("select count(1) as countSize from ( ? ) ");
        countSql.replace(countSql.indexOf("?"),countSql.indexOf("?")+1,sql);
        logger.info(countSql.toString());
        Integer total = this.jdbcTemplate.queryForInt(countSql.toString(),parameters);
        if(total == null || total == 0){
            return pageObject;
        }
        StringBuffer tmpSql = new StringBuffer("select * from ( :sql  ) where rowid in (select rid from ( select rownum rn, rowid rid from ( :sql ) where rownum <= ? ) where rn > ? )");
        tmpSql.replace(tmpSql.indexOf(":sql"),tmpSql.indexOf(":sql")+4,sql);
        tmpSql.replace(tmpSql.indexOf(":sql"),tmpSql.indexOf(":sql")+4,sql);
        List<Object> tmpparameters = new ArrayList<Object>();
        for(int i = 0 ;i < 2; i ++){
            for(Object param:parameters){
                tmpparameters.add(param);
            }
        }
        Integer rownumStart = rows*(page-1);
        Integer rownumEnd = rows*page;
        tmpparameters.add(rownumEnd);
        tmpparameters.add(rownumStart);

        List result = this.jdbcTemplate.query(tmpSql.toString(),rowMapper,tmpparameters.toArray());

        pageObject.setTotal(total);
        pageObject.setRowsList(result);

        return pageObject;

    }

    /**
     * 执行原生sql，返回Object数组集合，无需RowMapper
     * @param sql
     * @return
     * @throws BaseDaoException
     */
    protected List<Object[]> queryListSql(String sql) throws BaseDaoException{
        if(StringUtils.isBlank(sql)){throw new BaseDaoException("Invalid sql!");}
        RowMapper<Object[]> rowMapper = this.getObjectArrayRowMaper();
        List<Object[]> result = this.jdbcTemplate.query(sql,rowMapper);

        return result;
    }


    /**
     * 执行原生sql以及相应参数，返回Object数组集合，无需RowMapper
     * @param sql
     * @param parameters
     * @return
     * @throws BaseDaoException
     */
    protected List<Object[]> queryListSql(String sql,final Object...parameters) throws BaseDaoException{
        if(StringUtils.isBlank(sql)){throw new BaseDaoException("Invalid sql!");}
        if(parameters == null || parameters.length == 0){throw new BaseDaoException("Invalid parameters!");}
        RowMapper<Object[]> rowMapper = this.getObjectArrayRowMaper();

        List<Object[]> result = this.jdbcTemplate.query(sql,rowMapper ,parameters);

        return result;
    }


    private RowMapper<Object[]> getObjectArrayRowMaper(){
        RowMapper<Object[]> rowMapper  = new RowMapper() {
            public Object[] mapRow(ResultSet resultSet, int i) throws SQLException {
                if(resultSet == null){
                    return null;
                }
                Integer columns = resultSet.getRow();
                if(columns == null || columns <= 0){
                    return null;
                }
                Object[] objs= new Object[columns];
                for(int tmp = 0; tmp < columns ; tmp ++){
                    objs[tmp] = resultSet.getObject(tmp+1);
                }
                return objs;
            }
        };
        return rowMapper;
    }


    /**
     * 用于自定义dao方法
     * @return
     */
    protected JdbcTemplate getJdbcTemplate(){
        return this.jdbcTemplate;
    }


//    protected void setRowMapper(RowMapper<T> rowMapper) {
//        this.rowMapper = rowMapper;
//    }
//
//    protected void setTableName(String tableName) {
//        this.tableName = tableName;
//    }
//
//    protected void setIdName(String idName) {
//        this.idName = idName;
//    }
//
//    protected void setSequenceName(String sequenceName) {
//        this.sequenceName = sequenceName;
//    }
//
//    protected RowMapper<T> getRowMapper() {
//        return rowMapper;
//    }
//
//    protected String getTableName() {
//        return tableName;
//    }
//
//    protected String getIdName() {
//        return idName;
//    }
//
//    protected String getSequenceName() {
//        return sequenceName;
//    }

    protected BaseDaoInfo getDaoInfo() {
        return daoInfo;
    }

    protected void setDaoInfo(BaseDaoInfo daoInfo) throws BaseDaoException{
        if(daoInfo == null){
            throw new BaseDaoException("Invalid daoInfo!");
        }
        this.daoInfo = daoInfo;
    }
}
